package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import com.fact.dto.UserDTO;
import com.fact.utils.connection.ConnectionManager;

public class UserService {
	
	
	public UserDTO validateLogin(String userId,String password)
	{
		UserDTO user=null;
		
		Connection conn = ConnectionManager.getConnection();
		
		String sql = " Select * from MST_USER,MST_ROLE Where MST_ROLE.ROLE_ID=MST_USER.USER_TYPE And UserId=? and Password=?";
		 
		
		   PreparedStatement stmt = null;
		   ResultSet r = null;
		   
			try
			{
				stmt = conn.prepareStatement(sql);
				
			    stmt.setString(1, userId);
			    stmt.setString(2, password);

			    
				r = stmt.executeQuery();
				if (r.next())
				{
					user=new UserDTO();
					user.setUserId(r.getString("USERID"));
					user.setPassword(r.getString("PASSWORD"));
					user.setUserName(r.getString("USERNAME"));
					user.setIsActive(r.getInt("ISACTIVE"));
					user.setCreatedBy(r.getString("CREATED_BY"));
					user.setCreatedOn(r.getString("CREATED_ON"));
					user.setLastUpdatedBy(r.getString("LAST_UPDATED_BY"));
					user.setLastUpdatedOn(r.getString("LAST_UPDATED_ON"));
					user.setLastLoginDate(r.getString("LAST_LOGIN_DATE"));
					user.setLastLoginFrom(r.getString("LAST_LOGIN_FROM"));
					user.setUserTypeId(r.getInt("USER_TYPE"));
					user.setUserTypeName(r.getString("ROLE_NAME"));
					
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return user;
	}
	
	
	public ArrayList<UserDTO> getUserList()
	{
		UserDTO user=null;
		ArrayList<UserDTO> userList=new ArrayList<UserDTO>();
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from MST_USER,MST_ROLE Where MST_ROLE.ROLE_ID=MST_USER.USER_TYPE";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
		   int counter=1;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				while (r.next())
				{
					user=new UserDTO();
					user.setSlNo(counter);
					user.setUserId(r.getString("USERID"));
					user.setPassword(r.getString("PASSWORD"));
					user.setUserName(r.getString("USERNAME"));
					user.setIsActive(r.getInt("ISACTIVE"));
					user.setCreatedBy(r.getString("CREATED_BY"));
					user.setCreatedOn(r.getString("CREATED_ON"));
					user.setLastUpdatedBy(r.getString("LAST_UPDATED_BY"));
					user.setLastUpdatedOn(r.getString("LAST_UPDATED_ON"));
					user.setLastLoginDate(r.getString("LAST_LOGIN_DATE"));
					user.setLastLoginFrom(r.getString("LAST_LOGIN_FROM"));
					user.setUserTypeId(r.getInt("USER_TYPE"));
					user.setUserTypeName(r.getString("ROLE_NAME"));
					userList.add(user);
					counter++;
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return userList;
	}
	
	
	 
	public boolean validateUserId(String userId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql="Select count(*) total from MST_USER where USERID=?";
		int total=0;
		
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, userId);
				
				r = stmt.executeQuery();
				if (r.next())
				{
					total=r.getInt("total");
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		if(total>0)
			return false;
		else
			return true;

	}

	public boolean saveUser(UserDTO	user)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_USER(USERID,PASSWORD,USERNAME,ISACTIVE,CREATED_BY,CREATED_ON,USER_TYPE) " +
				   " values(?,?,?,?,?,sysdate,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,user.getUserId());
				stmt.setString(2,user.getPassword());
				stmt.setString(3,user.getUserName());
				stmt.setInt(4,user.getIsActive());
				stmt.setString(5,user.getCreatedBy());
				stmt.setInt(6,user.getUserTypeId());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteUser(String userId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_USER Where USERID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,userId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editUser(UserDTO user)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_USER  set PASSWORD=? , USERNAME=?,ISACTIVE=?,LAST_UPDATED_BY=?,LAST_UPDATED_ON=sysdate, " +
				"   USER_TYPE=? Where USERID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,user.getPassword());
				stmt.setString(2,user.getUserName());
				stmt.setInt(3,user.getIsActive());
				stmt.setString(4,user.getLastUpdatedBy());
				stmt.setInt(5,user.getUserTypeId());
				stmt.setString(6,user.getUserId());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	

	
	/*	
	public UserDTO getUser(String userId)
	{
		UserDTO user=null;
		Connection conn = ConnectionManager.getConnection();
		String sql = " Select * from MST_USER mu,MST_COUNTRY mc,MST_STATION ms,MST_USERTYPE mut " +
			" WHERE mu.USERROLE_ID=mut.USERROLE_ID And mu.COUNTRY_ID=mc.COUNTRY_ID " +
			" And mu.STATION_ID=ms.STATION_ID And USERID=?";
		
		
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);				
				stmt.setString(1, userId);				
				r = stmt.executeQuery();
				if (r.next())
				{
					user=new UserDTO();
					user.setUserId(r.getString("USERID"));
					user.setUserName(r.getString("USERNAME"));
					user.setCountryId(r.getString("COUNTRY_ID"));
					user.setStationId(r.getString("STATION_ID"));
					user.setUserRoleId(r.getString("USERROLE_ID"));
					user.setCustomerId(r.getString("CUSTOMER_ID"));
					user.setIsActive(r.getInt("ISACTIVE"));
					user.setCreatedOn(r.getString("CREATED_ON"));
					user.setCreatedBy(r.getString("CREATED_BY"));
					user.setUpdatedOn(r.getString("UPDATED_ON"));
					user.setUpdatedBy(r.getString("UPDATED_BY"));
					user.setLastLoginOn(r.getString("LAST_LOGIN_ON"));
					user.setLastLoginFrom(r.getString("LAST_LOGIN_FROM"));
					user.setCountryName(r.getString("COUNTRY_NAME"));
					user.setUserRoleName(r.getString("USERROLE_NAME"));
					user.setStationName(r.getString("STATION_NAME"));
					user.setEmailAddress(r.getString("EMAIL_ADDRESS"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		
		return user;
	}
	*/

}
